import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CountByMonth {
    public static void main(String[] args) {
        String sql = "select m.id '月份', IFNULL(b,0) '入学人数' from\n" +
                "(\n" +
                "  select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6\n" +
                "  union select 7 union select 8 union select 9 union select 10 union select 11 union select 12\n" +
                ") m\n" +
                "left join \n" +
                "(select month(s_student.enterdate) a,count(stuno) b from s_student \n" +
                "WHERE YEAR(enterdate )=2017\n" +
                "GROUP BY month(s_student.enterdate)) n \n" +
                "on m.id = n.a";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String city = rs.getString("月份");
                int count = rs.getInt("入学人数");
                System.out.println("月份：" + city + ",人数：" + count);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, con);
        }
    }
}

